using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using SMS.Common;
using SMS.Common.Data;
using SMS.Framework;

namespace SMS.MdiWindow
{
    public class DataAccess
    {
        public static List<MessageEntry> GetNewMessageEntries()
        {
            List<MessageEntry> messageEntryList = new List<MessageEntry>();
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from tbl_message where status = 'New' order by edit_date desc";
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            MessageEntry messageEntry = new MessageEntry(Convert.ToInt32(reader["sys_id"]),
                                (string)reader["msg_id"],
                                (string)reader["caption"],
                                (string)reader["category"],
                                (string)reader["content"],
                                (string)reader["sender"],
                                (string)reader["sender_dept"],
                                Convert.ToDateTime(reader["edit_date"]),
                                (string)reader["status"]);
                            messageEntryList.Add(messageEntry);
                        }
                    }
                }
            }
            return messageEntryList;
        }

        public static List<MessageEntry> GetMessageEntries(string category, string status)
        {
            List<MessageEntry> messageEntryList = new List<MessageEntry>();
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from tbl_message where category='" +category +"' and status = '" + status + "'";
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            MessageEntry messageEntry = new MessageEntry((int)reader["sys_id"],
                                (string)reader["msg_id"],
                                (string)reader["caption"],
                                (string)reader["category"],
                                (string)reader["content"],
                                (string)reader["sender"],
                                (string)reader["sender_dept"],
                                (DateTime)reader["edit_date"],
                                (string)reader["status"]);
                            messageEntryList.Add(messageEntry);
                        }
                    }
                }
            }
            return messageEntryList;
        }

        public static IDataReader GetMessageCategory()
        {
            //List<String> categoryList = new List<String>();
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select distinct category from tbl_message where category not like 'System%' order by category";
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        return (IDataReader)reader;
                        //while (reader.Read())
                        //{
                        //    String category = (String)reader["category"];
                        //    categoryList.Add(category);
                        //}
                    }
                }
            }
            //return categoryList;
        }

        public static List<String> GetMessageStatus()
        {
            List<String> statusList = new List<String>();
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select distinct status from tbl_message order by status";
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            String status = (String)reader["status"];
                            statusList.Add(status);
                        }
                    }
                }
            }
            return statusList;
        }

        public static void SaveNewMessage(MessageEntry message)
        {
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand com1 = conn.CreateCommand())
                {
                    com1.CommandText = "insert into tbl_message(msg_id,caption,category,content,sender,sender_dept,edit_date,status) values(?,?,?,?,?,?,?,?)";
                    DbParameter field1 = com1.CreateParameter();
                    DbParameter field2 = com1.CreateParameter();
                    DbParameter field3 = com1.CreateParameter();
                    DbParameter field4 = com1.CreateParameter();
                    DbParameter field5 = com1.CreateParameter();
                    DbParameter field6 = com1.CreateParameter();
                    DbParameter field7 = com1.CreateParameter();
                    DbParameter field8 = com1.CreateParameter();
                    field1.Value = message.MessageId;
                    field2.Value = message.Caption;
                    field3.Value = message.Category;
                    field4.Value = message.Content;
                    field5.Value = message.Sender;
                    field6.Value = message.SenderDepartment;
                    field7.Value = message.EditDate.ToString("yyyy-MM-dd");
                    field8.Value = message.Status;
                    com1.Parameters.Add(field1);
                    com1.Parameters.Add(field2);
                    com1.Parameters.Add(field3);
                    com1.Parameters.Add(field4);
                    com1.Parameters.Add(field5);
                    com1.Parameters.Add(field6);
                    com1.Parameters.Add(field7);
                    com1.Parameters.Add(field8);
                    com1.ExecuteNonQuery();
                }
            }
        }

        public static void MarkRead(string messageId)
        {
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand com1 = conn.CreateCommand())
                {
                    com1.CommandText = "update tbl_message set status='Read' where msg_id=?";
                    DbParameter field1 = com1.CreateParameter();
                    field1.Value = messageId;
                    com1.Parameters.Add(field1);
                    //conn.Open();
                    com1.ExecuteNonQuery();
                }
            }
        }

        public static MessageEntry GetMessageEntryById(string messageId)
        {
            MessageEntry messageEntry = new MessageEntry();
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = "select * from tbl_message where msg_id = '" + messageId + "'";
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            messageEntry = new MessageEntry(Convert.ToInt32(reader["sys_id"]),
                                (string)reader["msg_id"],
                                (string)reader["caption"],
                                (string)reader["category"],
                                (string)reader["content"],
                                (string)reader["sender"],
                                (string)reader["sender_dept"],
                                Convert.ToDateTime(reader["edit_date"]),
                                (string)reader["status"]);
                        }
                    }
                }
            }
            return messageEntry;

        }

        public static void DeleteAll()
        {
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand com1 = conn.CreateCommand())
                {
                    com1.CommandText = "delete from tbl_message";
                    com1.ExecuteNonQuery();
                }
            }
        }


        public static void DeleteBefore(DateTime dt)
        {
            using (DbConnection conn = SQLite.GetDbConnection())
            {
                conn.ConnectionString = Context.Current.AppInfo.LocalDbConnectionString;
                conn.Open();
                using (DbCommand com1 = conn.CreateCommand())
                {
                    com1.CommandText = "delete from tbl_message where edit_date< ?";
                    DbParameter field1 = com1.CreateParameter();
                    field1.Value = dt.ToString("yyyy-MM-dd");
                    com1.Parameters.Add(field1);
                    com1.ExecuteNonQuery();
                }
            }
        }


    }
}
